import pandas as pd
import sqlalchemy # 使用sqlalchemy连接opengauss

def read_data(table):
    sql = "SELECT * FROM " + table
    # 设置openGauss数据库版本信息
    from sqlalchemy.dialects.postgresql.base import PGDialect
    PGDialect._get_server_version_info = lambda *args: (9, 2)
    # 构造连接字符串
    userName = 'myroot'
    password = 'myroot_123'
    dbHost = '10.10.74.207'
    dbPort = 15400
    dbName = 'app'
    DB_CONNECT = f'postgresql://{userName}:{password}@{dbHost}:{dbPort}/{dbName}'
    # 创建数据库连接
    conn_new = sqlalchemy.create_engine(DB_CONNECT)
    if conn_new:
        print("连接数据库成功")
    df = pd.read_sql(sql, conn_new)
    print(df.columns)
    return df

# 读取公司信息CompanyInfos，设备信息InstallInfos，运行时间信息TimeInfos，删除id列
CompanyInfos = read_data('companyinfos')
CompanyInfos.drop(columns='id',inplace=True)
InstallInfos = read_data('installinfos')
InstallInfos.drop(columns='id',inplace=True)
TimeInfos = read_data('timeinfos')
TimeInfos.drop(columns='id',inplace=True)
# 将表中的数据'worktime','standbytime'转换成浮点数
TimeInfos['worktime'] = TimeInfos['worktime'].astype('float64')
TimeInfos['standbytime'] = TimeInfos['standbytime'].astype('float64')
# 计算所需的 每天停机时间:'stoptime',开机率'startrate',工作效率:'efficiency'
TimeInfos['stoptime'] = round(24-(TimeInfos['worktime'] + TimeInfos['standbytime']),2)
TimeInfos['startrate'] = round(((TimeInfos['worktime'] + TimeInfos['standbytime'])/24) * 100,2)
TimeInfos['efficiency'] = round(TimeInfos['worktime']/(TimeInfos['worktime'] + TimeInfos['standbytime']) * 100,2)
TimeInfos.fillna(0,inplace=True)
# 获取TimeInfos表中的最大日期，并筛选出该日期对应的数据保存在day_timeinfos变量中
date = TimeInfos['datetime'].max()
day_timeinfos = TimeInfos[TimeInfos['datetime'] == date]
